﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using Maticsoft.DBUtility;//Please add references
namespace CustomsSystem.DAL
{
    /// <summary>
    /// 数据访问类:Bill
    /// </summary>
    public partial class BillService
    {
        public BillService()
        { }
        #region  BasicMethod
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int billID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from T_bill");
            strSql.Append(" where billID=@billID");
            SqlParameter[] parameters = {
					new SqlParameter("@billID", SqlDbType.Int,4)
			};
            parameters[0].Value = billID;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool ExistsSheetid(int sheetid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from T_bill");
            strSql.Append(" where sheetid=@sheetid");
            SqlParameter[] parameters = {
					new SqlParameter("@sheetid", SqlDbType.Int,4)
			};
            parameters[0].Value = sheetid ;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(string feetypeid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from T_bill");
            strSql.Append(" where feetypeid=@feetypeid");
            SqlParameter[] parameters = {
					new SqlParameter("@feetypeid", SqlDbType.VarChar,10)
			};
            parameters[0].Value = feetypeid;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(CustomsSystem.Model.Bill model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into T_bill(");
            strSql.Append("sheetID,cID,billTypeID,feeTypeID,sfeeAmount,afeeAmount,empID,payedID,auditEmpID,inputEmpNo,inputDate,remark,auditdate,remark1,remark2,payBank,invNO,payType,invType,checkOut)");
            strSql.Append(" values (");
            strSql.Append("@sheetID,@cID,@billTypeID,@feeTypeID,@sfeeAmount,@afeeAmount,@empID,@payedID,@auditEmpID,@inputEmpNo,@inputDate,@remark,@auditdate,@remark1,@remark2,@payBank,@invNO,@payType,@invType,@checkOut)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@sheetID", SqlDbType.Int,4),
					new SqlParameter("@cID", SqlDbType.Int,4),
					new SqlParameter("@billTypeID", SqlDbType.Int,4),
					new SqlParameter("@feeTypeID", SqlDbType.VarChar,6),
					new SqlParameter("@sfeeAmount", SqlDbType.Decimal,9),
					new SqlParameter("@afeeAmount", SqlDbType.Decimal,9),
					new SqlParameter("@empID", SqlDbType.Char,4),
					new SqlParameter("@payedID", SqlDbType.Int,4),
					new SqlParameter("@auditEmpID", SqlDbType.Char,4),
					new SqlParameter("@inputEmpNo", SqlDbType.Char,4),
					new SqlParameter("@inputDate", SqlDbType.DateTime),
					new SqlParameter("@remark", SqlDbType.VarChar,60),
					new SqlParameter("@auditdate", SqlDbType.DateTime),
					new SqlParameter("@remark1", SqlDbType.VarChar,200),
					new SqlParameter("@remark2", SqlDbType.VarChar,200),
					new SqlParameter("@payBank", SqlDbType.VarChar,100),
					new SqlParameter("@invNO", SqlDbType.VarChar,30),
					new SqlParameter("@payType", SqlDbType.Int,4),
					new SqlParameter("@invType", SqlDbType.Int,4),
					new SqlParameter("@checkOut", SqlDbType.Int,4)};
            parameters[0].Value = model.sheetID;
            parameters[1].Value = model.cID;
            parameters[2].Value = model.billTypeID;
            parameters[3].Value = model.feeTypeID;
            parameters[4].Value = model.sfeeAmount;
            parameters[5].Value = model.afeeAmount;
            parameters[6].Value = model.empID;
            parameters[7].Value = model.payedID;
            parameters[8].Value = model.auditEmpID;
            parameters[9].Value = model.inputEmpNo;
            parameters[10].Value = model.inputDate;
            parameters[11].Value = model.remark;
            parameters[12].Value = model.auditdate;
            parameters[13].Value = model.remark1;
            parameters[14].Value = model.remark2;
            parameters[15].Value = model.payBank;
            parameters[16].Value = model.invNO;
            parameters[17].Value = model.payType;
            parameters[18].Value = model.invType;
            parameters[19].Value = model.checkOut;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(CustomsSystem.Model.Bill model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update T_bill set ");
            strSql.Append("sheetID=@sheetID,");
            strSql.Append("cID=@cID,");
            strSql.Append("billTypeID=@billTypeID,");
            strSql.Append("feeTypeID=@feeTypeID,");
            strSql.Append("sfeeAmount=@sfeeAmount,");
            strSql.Append("afeeAmount=@afeeAmount,");
            strSql.Append("empID=@empID,");
            strSql.Append("payedID=@payedID,");
            strSql.Append("auditEmpID=@auditEmpID,");
            strSql.Append("inputEmpNo=@inputEmpNo,");
            strSql.Append("inputDate=@inputDate,");
            strSql.Append("remark=@remark,");
            strSql.Append("auditdate=@auditdate,");
            strSql.Append("remark1=@remark1,");
            strSql.Append("remark2=@remark2,");
            strSql.Append("payBank=@payBank,");
            strSql.Append("invNO=@invNO,");
            strSql.Append("payType=@payType,");
            strSql.Append("invType=@invType,");
            strSql.Append("checkOut=@checkOut");
            strSql.Append(" where billID=@billID");
            SqlParameter[] parameters = {
					new SqlParameter("@sheetID", SqlDbType.Int,4),
					new SqlParameter("@cID", SqlDbType.Int,4),
					new SqlParameter("@billTypeID", SqlDbType.Int,4),
					new SqlParameter("@feeTypeID", SqlDbType.VarChar,6),
					new SqlParameter("@sfeeAmount", SqlDbType.Decimal,9),
					new SqlParameter("@afeeAmount", SqlDbType.Decimal,9),
					new SqlParameter("@empID", SqlDbType.Char,4),
					new SqlParameter("@payedID", SqlDbType.Int,4),
					new SqlParameter("@auditEmpID", SqlDbType.Char,4),
					new SqlParameter("@inputEmpNo", SqlDbType.Char,4),
					new SqlParameter("@inputDate", SqlDbType.DateTime),
					new SqlParameter("@remark", SqlDbType.VarChar,60),
					new SqlParameter("@auditdate", SqlDbType.DateTime),
					new SqlParameter("@remark1", SqlDbType.VarChar,200),
					new SqlParameter("@remark2", SqlDbType.VarChar,200),
					new SqlParameter("@payBank", SqlDbType.VarChar,100),
					new SqlParameter("@invNO", SqlDbType.VarChar,30),
					new SqlParameter("@payType", SqlDbType.Int,4),
					new SqlParameter("@invType", SqlDbType.Int,4),
					new SqlParameter("@checkOut", SqlDbType.Int,4),
					new SqlParameter("@billID", SqlDbType.Int,4)};
            parameters[0].Value = model.sheetID;
            parameters[1].Value = model.cID;
            parameters[2].Value = model.billTypeID;
            parameters[3].Value = model.feeTypeID;
            parameters[4].Value = model.sfeeAmount;
            parameters[5].Value = model.afeeAmount;
            parameters[6].Value = model.empID;
            parameters[7].Value = model.payedID;
            parameters[8].Value = model.auditEmpID;
            parameters[9].Value = model.inputEmpNo;
            parameters[10].Value = model.inputDate;
            parameters[11].Value = model.remark;
            parameters[12].Value = model.auditdate;
            parameters[13].Value = model.remark1;
            parameters[14].Value = model.remark2;
            parameters[15].Value = model.payBank;
            parameters[16].Value = model.invNO;
            parameters[17].Value = model.payType;
            parameters[18].Value = model.invType;
            parameters[19].Value = model.checkOut;
            parameters[20].Value = model.billID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 更新一条数据——由实收实付获得
        /// </summary>
        public bool UpdatePayedID(CustomsSystem.Model.Bill model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update T_bill set ");
            strSql.Append("payedID=@payedID,");
            strSql.Append("afeeAmount=@afeeAmount");
            strSql.Append(" where billID=@billID");
            SqlParameter[] parameters = {
                                             new SqlParameter("@payedID", SqlDbType.Int,4),
                                             new SqlParameter("@afeeAmount", SqlDbType.Decimal,10),
                                             new SqlParameter("@billID", SqlDbType.Int,4)};
            parameters[0].Value = model.payedID;
            parameters[1].Value = model.afeeAmount;
            parameters[2].Value = model.billID;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 根据sheetID和应收应付的类型，更新T_bill表中的payedID
        /// </summary>
        public void updatePayedID(int sheetID,int billTypeID)
        {
            string sqlStr = string.Format("update T_bill set payedID = {0} where sheetID = {1} and billTypeID = {2}",1,sheetID,billTypeID);
            DbHelperSQL.ExecuteSql(sqlStr);
        }

        /// <summary>
        /// 填入审核人
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool UpdateAudit(CustomsSystem.Model.Bill model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update T_bill set ");
            strSql.Append("auditEmpID=@auditEmpID,");
            strSql.Append(" auditdate=@auditdate");
            strSql.Append(" where sheetID=@sheetID");
            strSql.Append(" and billTypeID=@billTypeID");
            SqlParameter[] parameters = {
                                             new SqlParameter("@auditEmpID", SqlDbType.Char ,4),
                                            new SqlParameter("@sheetID", SqlDbType.Int,4),
                                             new SqlParameter("@billTypeID", SqlDbType.Int,4),
                                     new SqlParameter("@auditdate", SqlDbType.DateTime)};
            parameters[0].Value = model.auditEmpID;
            parameters[1].Value = model.sheetID;
            parameters[2].Value = model.billTypeID;
            parameters[3].Value = model.auditdate;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int billID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from T_bill ");
            strSql.Append(" where billID=@billID");
            SqlParameter[] parameters = {
					new SqlParameter("@billID", SqlDbType.Int,4)
			};
            parameters[0].Value = billID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int sheetID, int billTypeID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from T_bill ");
            strSql.Append(" where sheetID=" + sheetID + "");
            strSql.Append("and billTypeID=" + billTypeID + "");
            SqlParameter[] parameters = {
					new SqlParameter("@sheetID", SqlDbType.Int,4),
                    new SqlParameter("@billTypeID", SqlDbType.Int,4)
			};
            parameters[0].Value = sheetID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string billIDlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from T_bill ");
            strSql.Append(" where billID in (" + billIDlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public CustomsSystem.Model.Bill GetModel(int billID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 billID,sheetID,cID,billTypeID,feeTypeID,sfeeAmount,afeeAmount,empID,payedID,auditEmpID,inputEmpNo,inputDate,remark,auditdate,remark1,remark2,payBank,invNO,payType,invType,checkOut from T_bill ");
            strSql.Append(" where billID=@billID");
            SqlParameter[] parameters = {
					new SqlParameter("@billID", SqlDbType.Int,4)
			};
            parameters[0].Value = billID;

            CustomsSystem.Model.Bill model = new CustomsSystem.Model.Bill();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }



        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public CustomsSystem.Model.Bill DataRowToModel(DataRow row)
        {
            CustomsSystem.Model.Bill model = new CustomsSystem.Model.Bill();
            if (row != null)
            {
                if (row["billID"] != null && row["billID"].ToString() != "")
                {
                    model.billID = int.Parse(row["billID"].ToString());
                }
                if (row["sheetID"] != null && row["sheetID"].ToString() != "")
                {
                    model.sheetID = int.Parse(row["sheetID"].ToString());
                }
                if (row["cID"] != null && row["cID"].ToString() != "")
                {
                    model.cID = int.Parse(row["cID"].ToString());
                }
                if (row["billTypeID"] != null && row["billTypeID"].ToString() != "")
                {
                    model.billTypeID = int.Parse(row["billTypeID"].ToString());
                }
                if (row["feeTypeID"] != null)
                {
                    model.feeTypeID = row["feeTypeID"].ToString();
                }
                if (row["sfeeAmount"] != null && row["sfeeAmount"].ToString() != "")
                {
                    model.sfeeAmount = decimal.Parse(row["sfeeAmount"].ToString());
                }
                if (row["afeeAmount"] != null && row["afeeAmount"].ToString() != "")
                {
                    model.afeeAmount = decimal.Parse(row["afeeAmount"].ToString());
                }
                if (row["empID"] != null)
                {
                    model.empID = row["empID"].ToString();
                }
                if (row["payedID"] != null && row["payedID"].ToString() != "")
                {
                    model.payedID = int.Parse(row["payedID"].ToString());
                }
                if (row["auditEmpID"] != null)
                {
                    model.auditEmpID = row["auditEmpID"].ToString();
                }
                if (row["inputEmpNo"] != null)
                {
                    model.inputEmpNo = row["inputEmpNo"].ToString();
                }
                if (row["inputDate"] != null && row["inputDate"].ToString() != "")
                {
                    model.inputDate = DateTime.Parse(row["inputDate"].ToString());
                }
                if (row["remark"] != null)
                {
                    model.remark = row["remark"].ToString();
                }
                if (row["auditdate"] != null && row["auditdate"].ToString() != "")
                {
                    model.auditdate = DateTime.Parse(row["auditdate"].ToString());
                }
                if (row["remark1"] != null)
                {
                    model.remark1 = row["remark1"].ToString();
                }
                if (row["remark2"] != null)
                {
                    model.remark2 = row["remark2"].ToString();
                }
                if (row["payBank"] != null)
                {
                    model.payBank = row["payBank"].ToString();
                }
                if (row["invNO"] != null)
                {
                    model.invNO = row["invNO"].ToString();
                }
                if (row["payType"] != null && row["payType"].ToString() != "")
                {
                    model.payType = int.Parse(row["payType"].ToString());
                }
                if (row["invType"] != null && row["invType"].ToString() != "")
                {
                    model.invType = int.Parse(row["invType"].ToString());
                }
            }
            return model;
        }


        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        public DataSet getBillID(int sheetID,int billTypeID)
        {
            string sqlStr = string.Format("select billID from T_bill where sheetID = {0} and billTypeID = {1}",sheetID,billTypeID);
            return DbHelperSQL.Query(sqlStr);
        }

        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果PayedID（object）。
        /// </summary>
        public object GetPayedID(string SQLString)
        {
            int sheetID = int.Parse(SQLString);
            SQLString = "select count(distinct payedID) from T_bill where sheetID = " + sheetID + "";
            return DbHelperSQL.GetSingle(SQLString);
        }
        /// <summary>
        /// 执行一条查询是否全部销账，返回查询结果billTypeId。
        /// </summary>
        public DataSet  GetbillTypeId(int sheetid)
        {
            int sheetID = sheetid ;
            string strsql = "select distinct billTypeID  from T_bill where sheetID ="+sheetid +" and payedID =1";
            return DbHelperSQL.Query (strsql );
        }

        /// <summary>
        /// 执行一条查询有录入，返回查询结果billTypeId。
        /// </summary>
        public DataSet GetbillTypeId2(int sheetid)
        {
            int sheetID = sheetid;
            string strsql = "select distinct billTypeID  from T_bill where sheetID =" + sheetid + "";
            return DbHelperSQL.Query(strsql);
        }

        /// <summary>
        /// 查询该sheetID是否有费用数据。
        /// </summary>
        public object GetSfeeAmount(int sheetID,int type)
        {
            string SQLString = string.Format("select sfeeAmount from T_bill where sheetID = {0} and billTypeID = {1}",sheetID,type);
            return DbHelperSQL.GetSingle(SQLString);
        }

        /// <summary>
        /// 查询该委托单是否已审核。
        /// </summary>
        public object GetAuditEmpID(int sheetID, int type)
        {
            string SQLString = string.Format("select auditEmpID from T_bill where sheetID = {0} and billTypeID = {1}", sheetID, type);
            return DbHelperSQL.GetSingle(SQLString);
        }


        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select billID,sheetID,cID,billTypeID,feeTypeID,sfeeAmount,afeeAmount,empID,payedID,auditEmpID,inputEmpNo,inputDate,remark,auditdate,remark1,remark2,payBank,invNO,payType,invType ");
            strSql.Append(" FROM T_bill ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        /// <summary>
        /// 获取票结列表
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public DataSet GetTicketList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select a.sheetID AS 委托单单号,agencyDate AS 日期,a.goodsName AS 货名,grossWeight AS 重量,customsNo AS 报关单号,FeeName ,sfeeAmount");
            strSql.Append(" from T_sheetInfo a left join T_bill b ");
            strSql.Append("on (a.sheetID=b.sheetID)");
            strSql.Append("left join Info_feeType c on (b.feeTypeID=c.feeTypeNo)");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where BILLTYPEID=1  and" + strWhere);
            }


            return DbHelperSQL.Query(strSql.ToString());
        }


        /// <summary>
        /// 获取费用列表
        /// </summary>
        public DataSet GetFeeList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select a.billID,a.auditEmpID,b.feeName AS 费用类别,c.feeName AS 项目,a.sfeeAmount AS '金额RMB',a.remark AS 备注");
            strSql.Append(" from T_bill a left join Info_feeType b ");
            strSql.Append(" on(LEFT (a.feeTypeID ,2)=b.feeTypeNo )");
            strSql.Append(" left join Info_feeType c on a.feeTypeID =c.feeTypeNo ");
            strSql.Append(" where ");

            if (strWhere.Trim() != "")
            {
                strSql.Append(strWhere);
            }
            strSql.Append(" order by a.feeTypeID ");
            return DbHelperSQL.Query(strSql.ToString());
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(int sheetID, int billTypeID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select billID,sheetID,cID,billTypeID,feeTypeID,sfeeAmount,afeeAmount,empID,payedID,auditEmpID,inputEmpNo,inputDate,remark,auditdate,remark1,remark2,payBank,invNO,payType,invType,checkOut  ");
            strSql.Append(" FROM T_bill ");
            strSql.Append(" where sheetID=" + sheetID + "");
            strSql.Append("and billTypeID=" + billTypeID + " and feeTypeID!='0'");
            //strSql.Append("and auditEmpID<>'0'");
            return DbHelperSQL.Query(strSql.ToString());
        }
        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" billID,sheetID,cID,billTypeID,feeTypeID,sfeeAmount,afeeAmount,empID,payedID,auditEmpID,inputEmpNo,inputDate,remark,auditdate,remark1,remark2,payBank,invNO,payType,invType,checkOut ");
            strSql.Append(" FROM T_bill ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM T_bill ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.billID desc");
            }
            strSql.Append(")AS Row, T.*  from T_bill T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString());
        }
        /// <summary>
        /// 获取月结表的数据
        /// </summary>
        /// <param name="strFeetype">内外部费用</param>
        /// <param name="agencyDate">日期</param>
        /// <param name="cid">公司的编号</param>
        /// <param name="strDate">排序</param>
        /// <returns></returns>
        public DataSet GetPivot(string strFeetype, string agencyDate, string cid ,string strDate)
        {
            StringBuilder strSql = new StringBuilder();
            //strSql.Append(" declare @sql varchar(8000)");
            //strSql.Append(" with bill as(select distinct(b.feeName)");
            //strSql.Append("  from T_bill a inner join Info_feeType b on(a.feeTypeID=b.feeTypeNo) left join T_sheetInfo c on (a.sheetID =c.sheetID )");
            //strSql.Append(" WHERE billTypeID =1 ");//这里删掉了部分搜索条件
            //if (!string.IsNullOrEmpty(cid))
            //{
            //    strSql.Append(cid);
            //}
            //if (!string.IsNullOrEmpty(strFeetype))
            //{
            //    strSql.Append(strFeetype);
            //}
            //if (!string.IsNullOrEmpty(agencyDate))
            //{
            //    strSql.Append(agencyDate);
            //}
            //strSql.Append(" ) select @sql=ISNULL(@sql+',','')+ isnull('['+feename+']','') from bill ");

            //strSql.Append(" set @sql='SELECT M.* ,N.合计,remark AS 备注 FROM(");
            //strSql.Append(" select  Z.sheetID AS 委托单号  , agencyDate AS 日期,PO AS 订单号,ladingBillNo AS 提单号,contractNo AS 合同号,shipName AS 船名,goodsName AS 货名,Destination AS 目的地,No AS 数量,customsNo as 报关单号,inspectionNo AS 报检号,Z.remark,");
            //strSql.Append("  '+@sql+' ");
            //strSql.Append("  from(select  sheetID,CONVERT (varchar(10),agencyDate,23) AS agencyDate,PO,contractNo,shipName,goodsName,Destination,No,customsNo,inspectionNo,remark,ladingBillNo  from T_sheetInfo ");
            //strSql.Append("  )Z LEFT JOIN  (select T_bill.sheetID,feeName,T_bill.sfeeAmount");
            //strSql.Append("  from T_bill inner join Info_feeType  on(T_bill.feeTypeID=Info_feeType.feeTypeNo))c");
            //strSql.Append("  pivot( sum(sfeeAmount) for c.feeName in ('+@sql+'))");
            //strSql.Append("   d ON(Z.SHEETID=D.SHEETID)) m,(select SHEETID ,SUM(SfeeAmount) ");
            //strSql.Append("   合计 FROM T_BILL");
            //strSql.Append(" WHERE BILLTYPEID=1  " + strWhere);
            //strSql.Append(" GROUP BY SHEETID ) N WHERE M.委托单号 =N.SHEETID " + strDate + " '");

            //strSql.Append("exec (@sql)");
            strSql.Append("select  a.sheetID AS 委托单号,CONVERT (varchar(10),agencyDate,23) AS 日期,");
            strSql.Append("PO AS 订单号,contractNo AS 合同号,shipName AS 船名,goodsName AS 货名,Destination  AS 目的地,");
            strSql.Append("NO AS 数量,containerNo AS 集装箱号 ,grossWeight AS 毛重,agency AS 船舶代理,goodstype2 AS 货物种类,agency AS 危险品,customsNo AS 报关单号,inspectionNo  AS 报检号,");
            strSql.Append("ladingBillNo AS 提单号 ,c.feeName,b.sfeeAmount  from T_sheetInfo a  ");
            strSql.Append("inner JOIN T_bill b on(a.sheetID=b.sheetID) inner join Info_feeType c  on(b.feeTypeID=c.feeTypeNo)");
            strSql.Append("WHERE BILLTYPEID=1 ");
            if (strFeetype.Trim() != "")
            {
                strSql.Append(strFeetype);
            }
            if (cid.Trim() != "")
            {
                strSql.Append(" and a.cid=" + cid);
            }
            if (agencyDate.Trim() != "")
            {
                strSql.Append(agencyDate);
            }
            if (strDate.Trim()!= "")
            {
                strSql.Append(strDate);
            }

            return DbHelperSQL.Query(strSql.ToString());
        }
        /// <summary>
        /// 获取月结表(壳牌)的数据
        /// </summary>
        /// <param name="strFeetype">内外部费用</param>
        /// <param name="agencyDate">日期</param>
        /// <param name="cid">公司的编号</param>
        /// <param name="strDate">排序</param>
        /// <returns></returns>
        public DataSet GetPivot_QiaoPai(string strFeetype, string agencyDate, string cid, string strDate)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select  distinct(a.sheetID) AS 委托单号, CONVERT (varchar(10),agencyDate,23) AS 进口日期,contractNo AS 合同号, ");
            strSql.Append("PO AS 订单号,No AS '柜号/柜型',goodsName AS 货名,''送货地点    ");
            strSql.Append("  from T_sheetInfo a  inner JOIN T_bill b on(a.sheetID=b.sheetID)  ");
            strSql.Append(" inner join Info_feeType c  on(b.feeTypeID=c.feeTypeNo)  ");
            strSql.Append(" WHERE BILLTYPEID=1   ");

            if (strFeetype.Trim() != "")
            {
                strSql.Append(strFeetype);
            }
            if (cid.Trim() != "")
            {
                strSql.Append(" and a.cid=" + cid);
            }
            if (agencyDate.Trim() != "")
            {
                strSql.Append(agencyDate);
            }
            if (strDate.Trim() != "")
            {
                strSql.Append(strDate);
            }
            //费用类型费用
            strSql.Append(" select a.sheetID ,c.feeName,b.sfeeAmount  from T_sheetInfo a  inner JOIN   ");
            strSql.Append("   T_bill b on(a.sheetID=b.sheetID)  inner join Info_feeType c  on(b.feeTypeID=c.feeTypeNo)    ");
            strSql.Append(" WHERE BILLTYPEID=1  and (feeTypeID = 0205 or feeTypeID =011501)");
            //if (strFeetype.Trim() != "")
            //{
            //    strSql.Append(strFeetype);
            //}
            if (cid.Trim() != "")
            {
                strSql.Append(" and a.cid=" + cid);
            }
            if (agencyDate.Trim() != "")
            {
                strSql.Append(agencyDate);
            }
            
            //if (strDate.Trim() != "")
            //{
            //    strSql.Append(strDate);
            //}
            //代垫专票
            strSql.Append("select a.sheetID , sum  (sfeeAmount) '代垫费用(专票)' from T_bill a join T_sheetInfo b on a.sheetID =b.sheetID  ");
            strSql.Append("where checkOut =2 and  BILLTYPEID=1  and invType =2   ");
            
            //if (strFeetype.Trim() != "")
            //{
            //    strSql.Append(strFeetype);
            //}
            if (cid.Trim() != "")
            {
                strSql.Append(" and a.cid=" + cid);
            }
            if (agencyDate.Trim() != "")
            {
                strSql.Append(agencyDate);
            }
            strSql.Append(" and feeTypeID <>0205 and feeTypeID <>011501 group by a.sheetID,b.agencyDate   ");
            //if (strDate.Trim() != "")
            //{
            //    strSql.Append(strDate);
            //}
            

            //代垫普票
            strSql.Append("select a.sheetID , sum  (sfeeAmount) '代垫费用(普票)' from T_bill a join T_sheetInfo b on a.sheetID =b.sheetID  ");
            strSql.Append("where checkOut =2 and  BILLTYPEID=1  and invType =1   ");

            //if (strFeetype.Trim() != "")
            //{
            //    strSql.Append(strFeetype);
            //}
            if (cid.Trim() != "")
            {
                strSql.Append(" and a.cid=" + cid);
            }
            if (agencyDate.Trim() != "")
            {
                strSql.Append(agencyDate);
            }
            strSql.Append(" and feeTypeID <>0205 and feeTypeID <>011501 group by a.sheetID ,b.agencyDate  ");
            //if (strDate.Trim() != "")
            //{
            //    strSql.Append(strDate);
            //}

            return DbHelperSQL.Query(strSql.ToString());
        }
        /*
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize,int PageIndex,string strWhere)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@tblName", SqlDbType.VarChar, 255),
                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@PageIndex", SqlDbType.Int),
                    new SqlParameter("@IsReCount", SqlDbType.Bit),
                    new SqlParameter("@OrderType", SqlDbType.Bit),
                    new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
                    };
            parameters[0].Value = "T_bill";
            parameters[1].Value = "billID";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;	
            return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
        }*/

        #endregion  BasicMethod
        #region  ExtensionMethod

        #endregion  ExtensionMethod
    }
}

